import os
import os
import time
import shutil
import logging
import datetime
import platform
import psutil
import json
from pathlib import Path
from typing import List, Dict, Optional
from flask import Blueprint, render_template, redirect, url_for, session, flash, request, current_app, jsonify
import mysql.connector
import sys
import random
from datetime import datetime, timedelta, date
from werkzeug.utils import secure_filename
from auth import admin_required, get_db_connection
from functools import wraps
from enhanced_ops import EnhancedCloudOps

_admin_initialized = False

# 配置日志
logger = logging.getLogger('flask_system_ops')

# 确保蓝图正确定义
admin_bp = Blueprint('admin', __name__, url_prefix='/admin', template_folder='templates')

# 初始化增强的运维工具
cloud_ops = EnhancedCloudOps()


@admin_bp.before_app_request
def initialize_admin():
    global _admin_initialized
    if not _admin_initialized:
        # 修复：添加缩进的代码块
        initialize_ops()
        _admin_initialized = True


def initialize_ops():
    """在应用启动时初始化运维工具"""
    cloud_ops.app = current_app


# 允许上传的图片文件类型
ALLOWED_EXTENSIONS = {'png', 'jpg', 'jpeg', 'gif', 'webp'}


def allowed_file(filename):
    """检查文件扩展名是否允许"""
    return '.' in filename and \
           filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS


def save_room_image(file, room_id=None):
    """保存房间图片并返回文件名"""
    if not file or not allowed_file(file.filename):
        return None

    # 确保上传目录存在
    upload_folder = current_app.config.get('UPLOAD_FOLDER', 'uploads/rooms')
    os.makedirs(upload_folder, exist_ok=True)

    # 生成安全的文件名
    filename = secure_filename(file.filename)
    ext = filename.rsplit('.', 1)[1].lower()

    # 为不同房间生成唯一文件名
    if room_id:
        new_filename = f"room_{room_id}_{int(time.time())}.{ext}"
    else:
        new_filename = f"room_{int(time.time())}.{ext}"

    # 保存文件
    file_path = os.path.join(upload_folder, new_filename)
    file.save(file_path)

    return new_filename


def calculate_overall_stats(cursor, start, end, prev_start, prev_end,
                            occupancy_data, revenue_data, utilization_data,
                            room_types, start_str, end_str):
    """计算总体统计数据"""
    try:
        # 平均入住率
        avg_occupancy = sum(occupancy_data) / len(occupancy_data) if occupancy_data else 0

        # 总收入
        total_revenue = sum(revenue_data) if revenue_data else 0

        # 平均利用率
        avg_utilization = sum(utilization_data) / len(utilization_data) if utilization_data else 0

        # 总预订数
        cursor.execute("""
            SELECT COUNT(*) as total FROM bookings 
            WHERE status != 'cancelled'
            AND created_at BETWEEN %s AND %s
        """, (start_str, end_str))
        total_bookings = cursor.fetchone()['total'] or 0

        # 上期数据对比
        # 上期入住率
        cursor.execute("""
            SELECT MIN(DATE(created_at)) as min_date, 
                   MAX(DATE(created_at)) as max_date 
            FROM bookings 
            WHERE status != 'cancelled'
            AND created_at BETWEEN %s AND %s
        """, (prev_start.strftime('%Y-%m-%d %H:%M:%S'),
              prev_end.strftime('%Y-%m-%d %H:%M:%S')))
        prev_date_range = cursor.fetchone()

        prev_occupancy = 0
        prev_revenue = 0
        prev_bookings = 0

        if prev_date_range['min_date'] and prev_date_range['max_date']:
            # 上期入住率计算
            cursor.execute("SELECT COUNT(*) as total FROM rooms")
            total_rooms = cursor.fetchone()['total'] or 0

            if total_rooms > 0:
                cursor.execute("""
                    SELECT COUNT(DISTINCT room_id) as booked_count, 
                           DATE(check_in) as booking_date
                    FROM bookings 
                    WHERE status != 'cancelled'
                    AND check_in < %s
                    AND check_out > %s
                    GROUP BY DATE(check_in)
                """, (prev_end.strftime('%Y-%m-%d %H:%M:%S'),
                      prev_start.strftime('%Y-%m-%d %H:%M:%S')))
                prev_bookings_data = cursor.fetchall()
                if prev_bookings_data:
                    prev_occupancy = sum(
                        (item['booked_count'] / total_rooms) * 100
                        for item in prev_bookings_data
                    ) / len(prev_bookings_data)

            # 上期收入
            cursor.execute("""
                SELECT COALESCE(SUM(total_price), 0) as revenue 
                FROM bookings 
                WHERE status != 'cancelled'
                AND created_at BETWEEN %s AND %s
            """, (prev_start.strftime('%Y-%m-%d %H:%M:%S'),
                  prev_end.strftime('%Y-%m-%d %H:%M:%S')))
            prev_revenue = cursor.fetchone()['revenue'] or 0

            # 上期预订数
            cursor.execute("""
                SELECT COUNT(*) as total FROM bookings 
                WHERE status != 'cancelled'
                AND created_at BETWEEN %s AND %s
            """, (prev_start.strftime('%Y-%m-%d %H:%M:%S'),
                  prev_end.strftime('%Y-%m-%d %H:%M:%S')))
            prev_bookings = cursor.fetchone()['total'] or 0

        # 计算趋势和变化率
        def calculate_trend(current, previous):
            if previous == 0:
                return ('up', 100) if current > 0 else ('same', 0)
            change = ((current - previous) / previous) * 100
            if change > 0:
                return ('up', round(change, 1))
            elif change < 0:
                return ('down', round(abs(change), 1))
            else:
                return ('same', 0)

        occupancy_trend, occupancy_change = calculate_trend(avg_occupancy, prev_occupancy)
        receivable_trend, receivable_change = calculate_trend(total_revenue, prev_revenue)
        bookings_trend, bookings_change = calculate_trend(total_bookings, prev_bookings)
        utilization_trend, utilization_change = ('same', 0)  # 简化处理，实际应计算上期利用率

        return {
            'avg_occupancy_rate': round(avg_occupancy, 1),
            'total_receivable': total_revenue,
            'avg_utilization_rate': round(avg_utilization, 1),
            'total_bookings': total_bookings,
            'occupancy_trend': occupancy_trend,
            'occupancy_change': occupancy_change,
            'receivable_trend': receivable_trend,
            'receivable_change': receivable_change,
            'utilization_trend': utilization_trend,
            'utilization_change': utilization_change,
            'bookings_trend': bookings_trend,
            'bookings_change': bookings_change
        }
    except Exception as e:
        current_app.logger.error(f"计算总体统计失败: {str(e)}")
        # 返回默认值
        return {
            'avg_occupancy_rate': 0,
            'total_receivable': 0,
            'avg_utilization_rate': 0,
            'total_bookings': 0,
            'occupancy_trend': 'same',
            'occupancy_change': 0,
            'receivable_trend': 'same',
            'receivable_change': 0,
            'utilization_trend': 'same',
            'utilization_change': 0,
            'bookings_trend': 'same',
            'bookings_change': 0
        }


# ============================================================================
# 增强的系统运维路由 - 使用 EnhancedCloudOps 替换原有的 SystemOps
# ============================================================================

# 系统运维页面
@admin_bp.route('/system-ops')
@admin_required
def system_operations():
    """系统运维管理页面"""
    # 获取初始数据
    recent_errors = cloud_ops.get_recent_errors(10) if hasattr(cloud_ops, 'get_recent_errors') else []

    # 准备运维状态数据
    ops_status = {
        'is_running': False,  # EnhancedCloudOps 不维护运行状态
        'current_task': None,
        'last_run': cloud_ops.last_run if hasattr(cloud_ops, 'last_run') else None
    }

    return render_template(
        'admin/system_ops.html',
        ops_status=ops_status,
        recent_errors=recent_errors
    )


# 系统健康状态API - 增强版本
@admin_bp.route('/system-ops/health')
@admin_required
def system_health():
    """获取系统健康状态"""
    try:
        health_data = cloud_ops.get_system_health()
        return jsonify({
            'success': True,
            'health': health_data
        })
    except Exception as e:
        return jsonify({
            'success': False,
            'message': str(e)
        }), 500


# 获取系统日志API - 增强版本
@admin_bp.route('/system-ops/logs')
@admin_required
def system_logs():
    """获取系统日志错误"""
    try:
        # 使用 EnhancedCloudOps 的日志分析功能
        log_files = [f for f in os.listdir(cloud_ops.log_dir) if f.endswith('.log')]
        errors = []

        for log_file in log_files:
            log_path = os.path.join(cloud_ops.log_dir, log_file)
            analysis = cloud_ops.analyze_logs(log_path, lines_to_analyze=100)
            if 'recent_errors' in analysis:
                errors.extend(analysis['recent_errors'])

        # 按时间排序并限制数量
        errors.sort(key=lambda x: x.get('timestamp', ''), reverse=True)
        return jsonify({
            'success': True,
            'errors': errors[:20]
        })
    except Exception as e:
        return jsonify({
            'success': False,
            'message': str(e)
        }), 500


# 清理日志API - 增强版本
@admin_bp.route('/system-ops/logs/clean', methods=['POST'])
@admin_required
def clean_logs():
    """清理旧日志"""
    try:
        data = request.get_json()
        days = data.get('days', 30)

        if not isinstance(days, int) or days < 1:
            return jsonify({
                'success': False,
                'message': '天数必须是正整数'
            }), 400

        # EnhancedCloudOps 没有直接的日志清理方法，使用系统命令
        import subprocess
        if platform.system() == 'Linux':
            log_dir = cloud_ops.log_dir
            result = subprocess.run(
                f'find {log_dir} -name "*.log" -type f -mtime +{days} -delete',
                shell=True, capture_output=True, text=True
            )
            if result.returncode == 0:
                return jsonify({
                    'success': True,
                    'result': {
                        'status': 'success',
                        'message': f'已清理超过{days}天的日志文件',
                        'removed': '未知数量'
                    }
                })
            else:
                return jsonify({
                    'success': False,
                    'message': f'清理失败: {result.stderr}'
                }), 500
        else:
            return jsonify({
                'success': False,
                'message': '自动日志清理仅支持Linux系统'
            }), 400

    except Exception as e:
        return jsonify({
            'success': False,
            'message': str(e)
        }), 500


# 创建备份API - 增强版本
@admin_bp.route('/system-ops/backup', methods=['POST'])
@admin_required
def create_backup():
    """创建数据库备份(异步优化版)"""
    try:
        data = request.get_json() or {}
        backup_type = data.get('type', 'full')
        include_logs = data.get('include_logs', True)

        # 参数验证
        if backup_type not in ['full', 'incremental']:
            return jsonify({
                'success': False,
                'message': '不支持的备份类型'
            }), 400

        current_app.logger.info(f"开始创建备份: {backup_type}")
        
        # 直接调用优化后的备份方法
        result = cloud_ops.create_backup(backup_type, include_logs)

        if 'error' in result:
            current_app.logger.error(f"备份失败: {result['error']}")
            return jsonify({
                'success': False,
                'message': result['error']
            }), 500

        # 转换格式以保持兼容性
        backup_response = {
            'success': True,
            'backup': {
                'filename': result['backup_name'],
                'path': result['backup_path'],
                'size': result['size_human'],
                'created_at': result['created_at'],
                'file_count': len(result.get('files', []))
            }
        }

        return jsonify(backup_response)
    except Exception as e:
        current_app.logger.error(f"创建备份异常: {str(e)}")
        return jsonify({
            'success': False,
            'message': str(e)
        }), 500


# 获取备份列表API - 增强版本
@admin_bp.route('/system-ops/backups')
@admin_required
def get_backups():
    """获取备份列表(优化版 - 限制返回数量)"""
    try:
        # 获取分页参数
        limit = request.args.get('limit', 20, type=int)
        limit = max(1, min(limit, 50))  # 限制在1-50之间
        
        backups = cloud_ops.get_backup_list(limit=limit)

        # 转换格式以保持兼容性
        formatted_backups = []
        for backup in backups:
            formatted_backups.append({
                'name': backup['backup_name'],
                'path': backup.get('backup_path', ''),
                'size': backup.get('size_human', 'Unknown'),
                'created_at': backup.get('created_at', ''),
                'type': 'directory'
            })

        return jsonify({
            'success': True,
            'backups': formatted_backups,
            'total': len(formatted_backups)
        })
    except Exception as e:
        current_app.logger.error(f"获取备份列表失败: {str(e)}")
        return jsonify({
            'success': False,
            'message': str(e)
        }), 500


# 删除备份API - 增强版本
@admin_bp.route('/system-ops/backups/delete/<backup_name>', methods=['DELETE'])
@admin_required
def delete_backup(backup_name):
    """删除备份文件"""
    try:
        result = cloud_ops.delete_backup(backup_name)

        if 'error' in result:
            return jsonify({
                'success': False,
                'message': result['error']
            }), 500

        return jsonify({
            'success': True,
            'message': result.get('message', '备份已删除')
        })
    except Exception as e:
        return jsonify({
            'success': False,
            'message': str(e)
        }), 500


# 下载备份API - 增强版本
@admin_bp.route('/system-ops/backup/download/<backup_name>')
@admin_required
def download_backup(backup_name):
    """下载备份文件"""
    try:
        from flask import send_from_directory
        import zipfile

        backup_path = os.path.join(cloud_ops.backup_dir, backup_name)

        # 安全检查
        if not os.path.exists(backup_path) or not backup_name.startswith('backup_'):
            return jsonify({
                'success': False,
                'message': '备份文件不存在'
            }), 404

        # 如果是目录，创建zip文件
        if os.path.isdir(backup_path):
            zip_filename = f"{backup_name}.zip"
            zip_path = os.path.join(cloud_ops.backup_dir, zip_filename)

            with zipfile.ZipFile(zip_path, 'w', zipfile.ZIP_DEFLATED) as zipf:
                for root, dirs, files in os.walk(backup_path):
                    for file in files:
                        file_path = os.path.join(root, file)
                        arcname = os.path.relpath(file_path, backup_path)
                        zipf.write(file_path, arcname)

            return send_from_directory(
                cloud_ops.backup_dir,
                zip_filename,
                as_attachment=True
            )
        else:
            return send_from_directory(
                cloud_ops.backup_dir,
                backup_name,
                as_attachment=True
            )

    except Exception as e:
        return jsonify({
            'success': False,
            'message': str(e)
        }), 500


# 执行系统维护API - 增强版本
@admin_bp.route('/system-ops/maintenance', methods=['POST'])
@admin_required
def run_maintenance():
    """执行系统维护"""
    try:
        # 1. 创建备份
        backup_result = cloud_ops.create_backup()
        if 'error' in backup_result:
            return jsonify({
                'success': False,
                'message': f"备份失败: {backup_result['error']}"
            }), 500

        # 2. 清理旧备份（保留最近10个）
        cleanup_result = cloud_ops.cleanup_old_backups(keep_count=10)

        # 3. 启动系统监控
        monitor_result = cloud_ops.start_monitoring(interval=300)  # 5分钟间隔

        return jsonify({
            'success': True,
            'result': {
                'backup': backup_result,
                'cleanup': cleanup_result,
                'monitoring': monitor_result,
                'message': '系统维护已完成'
            }
        })
    except Exception as e:
        return jsonify({
            'success': False,
            'message': str(e)
        }), 500


# 新增：启动系统监控API
@admin_bp.route('/system-ops/monitoring/start', methods=['POST'])
@admin_required
def start_monitoring():
    """启动系统监控"""
    try:
        data = request.get_json() or {}
        interval = data.get('interval', 60)

        result = cloud_ops.start_monitoring(interval)
        return jsonify(result)
    except Exception as e:
        return jsonify({
            'success': False,
            'message': str(e)
        }), 500


# 新增：停止系统监控API
@admin_bp.route('/system-ops/monitoring/stop', methods=['POST'])
@admin_required
def stop_monitoring():
    """停止系统监控"""
    try:
        result = cloud_ops.stop_monitoring()
        return jsonify(result)
    except Exception as e:
        return jsonify({
            'success': False,
            'message': str(e)
        }), 500


# 新增：清理旧备份API
@admin_bp.route('/system-ops/cleanup/backups', methods=['POST'])
@admin_required
def cleanup_backups():
    """清理旧备份"""
    try:
        data = request.get_json() or {}
        keep_count = data.get('keep_count', 10)

        result = cloud_ops.cleanup_old_backups(keep_count)
        return jsonify(result)
    except Exception as e:
        return jsonify({
            'success': False,
            'message': str(e)
        }), 500


# 新增：服务状态检查API
@admin_bp.route('/system-ops/services/<service_name>')
@admin_required
def check_service(service_name):
    """检查服务状态"""
    try:
        result = cloud_ops.check_service_status(service_name)
        return jsonify({
            'success': True,
            'service_status': result
        })
    except Exception as e:
        return jsonify({
            'success': False,
            'message': str(e)
        }), 500


# ============================================================================
# 原有业务路由保持不变
# ============================================================================

# 报表页面
@admin_bp.route('/reports')
@admin_required
def reports():
    # 检查用户是否登录
    if 'username' not in session:
        return redirect(url_for('auth.login'))

    chart_data = None

    try:
        with get_db_connection() as conn:
            with conn.cursor(dictionary=True) as cursor:
                # 获取最早和最晚的日期来确定完整数据范围
                cursor.execute("""
                    SELECT MIN(DATE(created_at)) as min_date, 
                           MAX(DATE(created_at)) as max_date 
                    FROM bookings 
                    WHERE status != 'cancelled'
                """)
                date_range = cursor.fetchone()

                # 如果没有数据，设置默认日期范围
                if not date_range['min_date'] or not date_range['max_date']:
                    end = datetime.now()
                    start = end - timedelta(days=7)
                else:
                    # 处理日期类型转换
                    if isinstance(date_range['min_date'], date) and not isinstance(date_range['min_date'], datetime):
                        start = datetime.combine(date_range['min_date'], datetime.min.time())
                    else:
                        start = date_range['min_date']

                    if isinstance(date_range['max_date'], date) and not isinstance(date_range['max_date'], datetime):
                        end = datetime.combine(date_range['max_date'], datetime.max.time())
                    else:
                        end = date_range['max_date']

                # 生成所有日期标签
                date_labels = []
                current_date = start.date()
                end_date = end.date()
                while current_date <= end_date:
                    date_labels.append(current_date.strftime('%m-%d'))
                    current_date += timedelta(days=1)

                # 1. 入住率数据
                occupancy_data = []
                cursor.execute("SELECT COUNT(*) as total FROM rooms")
                total_rooms = cursor.fetchone()['total'] or 0

                if total_rooms > 0:
                    for d in date_labels:
                        # 解析日期并处理跨年情况
                        date_obj = datetime.strptime(f"{start.year}-{d}", '%Y-%m-%d').date()
                        if date_obj < start.date() and start.year < end.year:
                            date_obj = datetime.strptime(f"{start.year + 1}-{d}", '%Y-%m-%d').date()

                        date_str = date_obj.strftime('%Y-%m-%d')
                        cursor.execute("""
                            SELECT COUNT(DISTINCT room_id) as booked_count 
                            FROM bookings 
                            WHERE status != 'cancelled'
                            AND DATE(check_in) <= %s 
                            AND DATE(check_out) > %s
                        """, (date_str, date_str))
                        booked_rooms = cursor.fetchone()['booked_count'] or 0
                        occupancy_data.append(round((booked_rooms / total_rooms) * 100, 1))
                else:
                    occupancy_data = [0] * len(date_labels)

                # 2. 应收金额数据
                revenue_data = []
                for d in date_labels:
                    date_obj = datetime.strptime(f"{start.year}-{d}", '%Y-%m-%d').date()
                    if date_obj < start.date() and start.year < end.year:
                        date_obj = datetime.strptime(f"{start.year + 1}-{d}", '%Y-%m-%d').date()
                    date_str = date_obj.strftime('%Y-%m-%d')

                    cursor.execute("""
                        SELECT COALESCE(SUM(total_price), 0) as revenue 
                        FROM bookings 
                        WHERE status != 'cancelled'
                        AND DATE(created_at) = %s
                    """, (date_str,))
                    revenue = cursor.fetchone()['revenue'] or 0
                    revenue_data.append(int(revenue))

                # 3. 房间类型占比数据
                room_type_data = []
                room_type_labels = []
                type_name_map = {
                    'standard': '标准间',
                    'deluxe': '豪华间',
                    'suite': '套房',
                    'family': '家庭房',
                    'game': '电竞房'
                }

                cursor.execute("""
                    SELECT room_type, COUNT(*) as count 
                    FROM rooms 
                    GROUP BY room_type
                """)
                room_types = cursor.fetchall()
                for item in room_types:
                    room_type_labels.append(type_name_map.get(item['room_type'], item['room_type']))
                    room_type_data.append(item['count'])

                # 4. 房间利用率数据（按类型）
                utilization_data = []
                utilization_labels = room_type_labels.copy()
                start_str = start.strftime('%Y-%m-%d %H:%M:%S')
                end_str = end.strftime('%Y-%m-%d %H:%M:%S')
                period_length = (end.date() - start.date()).days + 1  # 包含首尾日期

                for item in room_types:
                    room_type = item['room_type']
                    type_rooms = item['count'] or 0

                    if type_rooms == 0:
                        utilization_data.append(0)
                        continue

                    # 获取该类型所有房间ID
                    cursor.execute("SELECT id FROM rooms WHERE room_type = %s", (room_type,))
                    type_room_ids = [row['id'] for row in cursor.fetchall()]

                    if not type_room_ids:
                        utilization_data.append(0)
                        continue

                    # 一次性获取所有相关预订
                    placeholders = ', '.join(['%s'] * len(type_room_ids))
                    cursor.execute(f"""
                        SELECT room_id, check_in, check_out 
                        FROM bookings 
                        WHERE room_id IN ({placeholders})
                        AND status != 'cancelled'
                        AND check_in < %s
                        AND check_out > %s
                    """, type_room_ids + [end_str, start_str])

                    bookings = cursor.fetchall()
                    booked_days = 0

                    # 计算总预订天数
                    for b in bookings:
                        # 处理日期类型
                        if isinstance(b['check_in'], str):
                            booking_start = datetime.strptime(b['check_in'], '%Y-%m-%d %H:%M:%S')
                        else:
                            booking_start = b['check_in']

                        if isinstance(b['check_out'], str):
                            booking_end = datetime.strptime(b['check_out'], '%Y-%m-%d %H:%M:%S')
                        else:
                            booking_end = b['check_out']

                        # 确保日期在统计范围内
                        booking_start = max(booking_start, start)
                        booking_end = min(booking_end, end)

                        # 计算天数差
                        days = (booking_end - booking_start).days
                        booked_days += max(0, days)

                    # 计算利用率
                    total_days = type_rooms * period_length
                    utilization_rate = round((booked_days / total_days) * 100, 1) if total_days > 0 else 0
                    utilization_data.append(utilization_rate)

                # 计算总体统计数据
                prev_start = start - (end - start)
                prev_end = start

                # 计算总体统计
                overall_stats = calculate_overall_stats(
                    cursor, start, end, prev_start, prev_end,
                    occupancy_data, revenue_data, utilization_data,
                    room_types, start_str, end_str
                )

                # 准备模板数据
                chart_data = {
                    'date_labels': date_labels,
                    'occupancy_data': occupancy_data,
                    'revenue_data': revenue_data,
                    'room_type_data': room_type_data,
                    'room_type_labels': room_type_labels,
                    'utilization_data': utilization_data,
                    'utilization_labels': utilization_labels,
                    'overall_stats': overall_stats
                }

                return render_template('admin/echart.html', chart_data=chart_data, error=None)

    except Exception as e:
        current_app.logger.error(f"生成报表数据失败: {str(e)}")

        # 生成模拟数据
        mock_end = datetime.now()
        mock_start = mock_end - timedelta(days=7)
        mock_date_labels = []
        current_date = mock_start.date()
        while current_date <= mock_end.date():
            mock_date_labels.append(current_date.strftime('%m-%d'))
            current_date += timedelta(days=1)

        # 模拟入住率数据
        mock_occupancy = [round(60 + (30 * random.random()), 1) for _ in mock_date_labels]

        # 模拟收入数据
        mock_revenue = [random.randint(5000, 15000) for _ in mock_date_labels]

        # 模拟房间类型数据
        mock_room_types = ['标准间', '豪华间', '套房', '家庭房']
        mock_room_type_data = [30, 15, 8, 5]

        # 模拟利用率数据
        mock_utilization = [round(50 + (40 * random.random()), 1) for _ in mock_room_types]

        # 模拟总体统计
        mock_overall = {
            'avg_occupancy_rate': round(sum(mock_occupancy) / len(mock_occupancy), 1),
            'total_receivable': sum(mock_revenue),
            'avg_utilization_rate': round(sum(mock_utilization) / len(mock_utilization), 1),
            'total_bookings': random.randint(120, 250),
            'occupancy_trend': 'up',
            'occupancy_change': round(5 + (10 * random.random()), 1),
            'receivable_trend': 'up',
            'receivable_change': round(8 + (15 * random.random()), 1),
            'utilization_trend': 'down',
            'utilization_change': round(2 + (5 * random.random()), 1),
            'bookings_trend': 'up',
            'bookings_change': round(6 + (12 * random.random()), 1)
        }

        chart_data = {
            'date_labels': mock_date_labels,
            'occupancy_data': mock_occupancy,
            'revenue_data': mock_revenue,
            'room_type_data': mock_room_type_data,
            'room_type_labels': mock_room_types,
            'utilization_data': mock_utilization,
            'utilization_labels': mock_room_types,
            'overall_stats': mock_overall
        }

        return render_template(
            'admin/echart.html',
            chart_data=chart_data,
            error="数据库数据获取失败，已加载模拟数据供参考"
        )


# 报表数据导出API（Excel格式）
@admin_bp.route('/api/reports/export')
@admin_required
def export_report_data():
    """导出报表数据为CSV格式"""
    from io import StringIO
    import csv
    from flask import make_response
    
    try:
        output = StringIO()
        writer = csv.writer(output)
        
        # 写入标题
        writer.writerow(['酒店管理系统 - 运营报表'])
        writer.writerow(['导出时间:', datetime.now().strftime('%Y-%m-%d %H:%M:%S')])
        writer.writerow([])
        
        with get_db_connection() as conn:
            with conn.cursor(dictionary=True) as cursor:
                # 导出总体统计
                writer.writerow(['=== 总体统计 ==='])
                
                cursor.execute("SELECT COUNT(*) as total FROM rooms")
                total_rooms = cursor.fetchone()['total']
                
                cursor.execute("SELECT COUNT(*) as total FROM bookings WHERE status != 'cancelled'")
                total_bookings = cursor.fetchone()['total']
                
                cursor.writerow(['总房间数', total_rooms])
                writer.writerow(['总预订数', total_bookings])
                writer.writerow([])
                
                # 导出房间类型统计
                writer.writerow(['=== 房间类型统计 ==='])
                writer.writerow(['房间类型', '数量', '占比'])
                
                cursor.execute("""
                    SELECT room_type, COUNT(*) as count 
                    FROM rooms 
                    GROUP BY room_type
                """)
                room_types = cursor.fetchall()
                
                type_name_map = {
                    'standard': '标准间',
                    'deluxe': '豪华间',
                    'suite': '套房',
                    'family': '家庭房',
                    'game': '电竞房'
                }
                
                for item in room_types:
                    type_name = type_name_map.get(item['room_type'], item['room_type'])
                    count = item['count']
                    percentage = round((count / total_rooms) * 100, 1) if total_rooms > 0 else 0
                    writer.writerow([type_name, count, f"{percentage}%"])
                
                writer.writerow([])
                
                # 导出最近订单
                writer.writerow(['=== 最近30天订单 ==='])
                writer.writerow(['订单ID', '房间名称', '入住日期', '退房日期', '总价', '状态'])
                
                cursor.execute("""
                    SELECT b.id, r.name, b.check_in, b.check_out, b.total_price, b.status
                    FROM bookings b
                    JOIN rooms r ON b.room_id = r.id
                    WHERE b.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
                    ORDER BY b.created_at DESC
                    LIMIT 100
                """)
                recent_bookings = cursor.fetchall()
                
                for booking in recent_bookings:
                    writer.writerow([
                        booking['id'],
                        booking['name'],
                        booking['check_in'],
                        booking['check_out'],
                        f"¥{booking['total_price']}",
                        booking['status']
                    ])
        
        # 准备下载响应
        output.seek(0)
        response = make_response(output.getvalue())
        filename = f"hotel_report_{datetime.now().strftime('%Y%m%d_%H%M%S')}.csv"
        response.headers["Content-Disposition"] = f"attachment; filename={filename}"
        response.headers["Content-type"] = "text/csv; charset=utf-8-sig"  # 支持中文
        
        return response
        
    except Exception as e:
        current_app.logger.error(f"导出报表失败: {str(e)}")
        return jsonify({
            'success': False,
            'message': f'导出失败: {str(e)}'
        }), 500


# 管理员仪表板
@admin_bp.route('/dashboard')
@admin_required
def dashboard():
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    try:
        # 获取统计数据
        cursor.execute("SELECT COUNT(*) as total_users FROM users WHERE role = 'user'")
        total_users = cursor.fetchone()['total_users']

        cursor.execute("SELECT COUNT(*) as total_rooms FROM rooms")
        total_rooms = cursor.fetchone()['total_rooms']

        cursor.execute("SELECT COUNT(*) as active_bookings FROM bookings WHERE status IN ('confirmed', 'checked_in')")
        active_bookings = cursor.fetchone()['active_bookings']

        cursor.execute("SELECT COUNT(*) as pending_feedbacks FROM feedbacks WHERE status = 'pending'")
        pending_feedbacks = cursor.fetchone()['pending_feedbacks']

        # 获取最近预订
        cursor.execute("""
            SELECT o.*, u.username, r.name as room_name 
            FROM bookings o 
            JOIN users u ON o.user_id = u.id 
            JOIN rooms r ON o.room_id = r.id 
            ORDER BY o.created_at DESC LIMIT 5
        """)
        recent_bookings = cursor.fetchall()

        return render_template('admin/dashboard.html',
                               total_users=total_users,
                               total_rooms=total_rooms,
                               active_bookings=active_bookings,
                               pending_feedbacks=pending_feedbacks,
                               recent_bookings=recent_bookings)
    except Exception as e:
        flash(f"加载仪表板失败: {str(e)}", 'danger')
        return render_template('admin/dashboard.html',
                               total_users=0,
                               total_rooms=0,
                               active_bookings=0,
                               pending_feedbacks=0,
                               recent_bookings=[])
    finally:
        cursor.close()
        conn.close()


# 管理房间（带搜索和分页功能）
@admin_bp.route('/rooms')
@admin_required
def manage_rooms():
    # 分页配置
    PER_PAGE = 8

    # 获取搜索参数
    search_params = {
        'keyword': request.args.get('keyword', '').strip(),
        'price_range': request.args.get('price_range', 'all'),
        'room_type': request.args.get('room_type', 'all')
    }

    # 获取当前页码
    try:
        current_page = int(request.args.get('page', 1))
        current_page = max(1, current_page)
    except ValueError:
        current_page = 1

    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    try:
        # 构建查询条件
        query_conditions = []
        query_params = []

        # 关键词搜索
        if search_params['keyword']:
            query_conditions.append("(name LIKE %s OR description LIKE %s)")
            search_term = f"%{search_params['keyword']}%"
            query_params.extend([search_term, search_term])

        # 价格范围筛选
        price_filters = {
            '0-200': "price BETWEEN 0 AND 200",
            '200-500': "price BETWEEN 200 AND 500",
            '500-1000': "price BETWEEN 500 AND 1000",
            '1000+': "price >= 1000"
        }
        if search_params['price_range'] in price_filters:
            query_conditions.append(price_filters[search_params['price_range']])

        # 房间类型筛选
        if search_params['room_type'] != 'all':
            query_conditions.append("room_type = %s")
            query_params.append(search_params['room_type'])

        # 查询符合条件的总记录数
        count_query = "SELECT COUNT(*) as total FROM rooms"
        if query_conditions:
            count_query += " WHERE " + " AND ".join(query_conditions)

        cursor.execute(count_query, query_params.copy())
        total_items = cursor.fetchone()['total']

        # 计算分页参数
        total_pages = (total_items + PER_PAGE - 1) // PER_PAGE
        current_page = min(current_page, total_pages) if total_pages > 0 else 1
        offset = (current_page - 1) * PER_PAGE

        # 查询当前页的数据
        base_query = "SELECT * FROM rooms"
        if query_conditions:
            data_query = f"{base_query} WHERE {' AND '.join(query_conditions)} ORDER BY created_at DESC LIMIT %s OFFSET %s"
        else:
            data_query = f"{base_query} ORDER BY created_at DESC LIMIT %s OFFSET %s"

        query_params.extend([PER_PAGE, offset])
        cursor.execute(data_query, query_params)
        rooms = cursor.fetchall()

        # 生成页码导航范围
        page_range = []
        if total_pages <= 5:
            page_range = list(range(1, total_pages + 1))
        else:
            page_range.append(1)
            if current_page > 3:
                page_range.append('...')

            start_page = max(2, current_page - 2)
            end_page = min(total_pages - 1, current_page + 2)
            page_range.extend(range(start_page, end_page + 1))

            if current_page < total_pages - 2:
                page_range.append('...')
            page_range.append(total_pages)

        # 计算当前页显示的记录范围
        start_item = offset + 1 if total_items > 0 else 0
        end_item = min(offset + PER_PAGE, total_items)

        return render_template('admin/rooms.html',
                               rooms=rooms,
                               search_params=search_params,
                               total_items=total_items,
                               current_page=current_page,
                               total_pages=total_pages,
                               page_range=page_range,
                               start_item=start_item,
                               end_item=end_item)
    except Exception as e:
        flash(f"加载房间列表失败: {str(e)}", 'danger')
        return render_template('admin/rooms.html',
                               rooms=[],
                               search_params=search_params,
                               total_items=0,
                               current_page=1,
                               total_pages=0,
                               page_range=[],
                               start_item=0,
                               end_item=0)
    finally:
        cursor.close()
        conn.close()


# 添加/编辑房间
@admin_bp.route('/rooms/add', methods=['GET', 'POST'])
@admin_bp.route('/rooms/edit/<int:room_id>', methods=['GET', 'POST'])
@admin_required
def manage_room(room_id=None):
    # 处理表单提交
    if request.method == 'POST':
        try:
            # 获取表单数据
            name = request.form.get('name', '').strip()
            description = request.form.get('description', '').strip()
            price = float(request.form.get('price', 0))
            capacity = int(request.form.get('capacity', 0))
            amenities = request.form.get('amenities', '').strip()
            status = request.form.get('status', 'available')
            size = float(request.form.get('size', 0))
            room_type = request.form.get('room_type', 'standard')
            
            # 获取选中的设施ID列表
            selected_amenities = request.form.getlist('amenity_ids[]')

            # 验证必填字段
            if not all([name, price > 0, capacity > 0, size > 0]):
                flash('房间名称、价格、容量和面积为必填项，且必须为正数', 'error')
                return redirect(request.url)

            # 处理图片上传
            image_url = None
            if 'image' in request.files and request.files['image'].filename:
                file = request.files['image']
                if not allowed_file(file.filename):
                    flash('不支持的文件类型，仅允许 PNG, JPG, JPEG, GIF, WebP', 'error')
                    return redirect(request.url)

                # 编辑时删除旧图片
                if room_id:
                    with get_db_connection() as conn:
                        with conn.cursor() as cursor:
                            cursor.execute("SELECT image_url FROM rooms WHERE id = %s", (room_id,))
                            old_image = cursor.fetchone()

                            if old_image and old_image[0]:
                                old_image_path = os.path.join(current_app.config['UPLOAD_FOLDER'], old_image[0])
                                if os.path.exists(old_image_path):
                                    os.remove(old_image_path)

                # 保存新图片
                image_url = save_room_image(file, room_id)

            # 数据库操作
            with get_db_connection() as conn:
                with conn.cursor() as cursor:
                    if room_id:  # 编辑房间
                        if image_url:
                            cursor.execute("""
                                UPDATE rooms SET 
                                    name=%s, description=%s, price=%s, capacity=%s, 
                                    amenities=%s, status=%s, image_url=%s, size=%s, room_type=%s
                                WHERE id=%s
                            """, (name, description, price, capacity, amenities,
                                  status, image_url, size, room_type, room_id))
                        else:
                            cursor.execute("""
                                UPDATE rooms SET 
                                    name=%s, description=%s, price=%s, capacity=%s, 
                                    amenities=%s, status=%s, size=%s, room_type=%s
                                WHERE id=%s
                            """, (name, description, price, capacity, amenities,
                                  status, size, room_type, room_id))
                        
                        # 更新房间设施关联
                        # 先删除旧的关联
                        cursor.execute("DELETE FROM room_amenities WHERE room_id = %s", (room_id,))
                    else:  # 添加新房间
                        cursor.execute("""
                            INSERT INTO rooms (
                                name, description, price, capacity, amenities, 
                                status, image_url, size, room_type, created_at
                            ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, NOW())
                        """, (name, description, price, capacity, amenities,
                              status, image_url, size, room_type))
                        room_id = cursor.lastrowid
                    
                    # 添加新的设施关联
                    if selected_amenities:
                        for amenity_id in selected_amenities:
                            cursor.execute("""
                                INSERT INTO room_amenities (room_id, amenity_id) 
                                VALUES (%s, %s)
                            """, (room_id, int(amenity_id)))

                conn.commit()
                flash('房间信息已成功保存', 'success')
                return redirect(url_for('admin.manage_rooms'))

        except ValueError:
            flash('数据格式错误：价格、容量和面积必须为数字', 'error')
        except FileNotFoundError:
            flash('上传目录不存在，请联系系统管理员', 'error')
        except Exception as e:
            flash(f'操作失败: {str(e)}', 'error')
        return redirect(request.url)

    # GET请求 - 显示表单
    room = None
    room_amenity_ids = []
    
    if room_id:
        try:
            with get_db_connection() as conn:
                with conn.cursor(dictionary=True) as cursor:
                    cursor.execute("SELECT * FROM rooms WHERE id = %s", (room_id,))
                    room = cursor.fetchone()
                    
                    # 获取房间已关联的设施ID
                    cursor.execute("SELECT amenity_id FROM room_amenities WHERE room_id = %s", (room_id,))
                    room_amenity_ids = [row['amenity_id'] for row in cursor.fetchall()]

            if not room:
                flash('未找到指定房间', 'error')
                return redirect(url_for('admin.manage_rooms'))

        except Exception as e:
            flash(f'加载房间信息失败: {str(e)}', 'error')
            return redirect(url_for('admin.manage_rooms'))
    
    # 获取所有可用设施（按分类分组）
    try:
        with get_db_connection() as conn:
            with conn.cursor(dictionary=True) as cursor:
                cursor.execute("SELECT * FROM amenities ORDER BY category, name")
                all_amenities = cursor.fetchall()
                
                # 按分类分组
                amenities_by_category = {}
                for amenity in all_amenities:
                    category = amenity['category']
                    if category not in amenities_by_category:
                        amenities_by_category[category] = []
                    amenities_by_category[category].append(amenity)
    except Exception as e:
        flash(f'加载设施列表失败: {str(e)}', 'warning')
        amenities_by_category = {}

    return render_template('admin/room_form.html', 
                         room=room, 
                         amenities_by_category=amenities_by_category,
                         room_amenity_ids=room_amenity_ids)


# 删除房间
@admin_bp.route('/rooms/delete/<int:room_id>')
@admin_required
def delete_room(room_id):
    conn = get_db_connection()
    cursor = conn.cursor()

    try:
        # 检查是否有关联预订
        cursor.execute("SELECT COUNT(*) as count FROM bookings WHERE room_id = %s", (room_id,))
        booking_count = cursor.fetchone()[0]
        if booking_count > 0:
            flash(f'无法删除：该房间仍有 {booking_count} 个关联预订', 'error')
            return redirect(url_for('admin.manage_rooms'))

        # 获取并删除图片
        cursor.execute("SELECT image_url FROM rooms WHERE id = %s", (room_id,))
        result = cursor.fetchone()
        if result and result[0]:
            file_path = os.path.join(current_app.config['UPLOAD_FOLDER'], result[0])
            if os.path.exists(file_path):
                os.remove(file_path)

        # 删除房间记录
        cursor.execute("DELETE FROM rooms WHERE id = %s", (room_id,))
        conn.commit()
        flash('房间已成功删除', 'success')

    except Exception as e:
        conn.rollback()
        flash(f'删除失败: {str(e)}', 'error')
    finally:
        cursor.close()
        conn.close()

    return redirect(url_for('admin.manage_rooms'))


# 删除房间图片
@admin_bp.route('/rooms/delete_image/<int:room_id>', methods=['POST'])
@admin_required
def delete_room_image(room_id):
    conn = get_db_connection()
    cursor = conn.cursor()

    try:
        # 获取图片路径
        cursor.execute("SELECT image_url FROM rooms WHERE id = %s", (room_id,))
        result = cursor.fetchone()

        if result and result[0]:
            # 删除图片文件
            file_path = os.path.join(current_app.config['UPLOAD_FOLDER'], result[0])
            if os.path.exists(file_path):
                os.remove(file_path)

            # 清空数据库中的图片路径
            cursor.execute("UPDATE rooms SET image_url = NULL WHERE id = %s", (room_id,))
            conn.commit()
            flash('房间图片已删除', 'success')
        else:
            flash('该房间没有图片', 'error')

    except Exception as e:
        conn.rollback()
        flash(f'操作失败: {str(e)}', 'error')
    finally:
        cursor.close()
        conn.close()

    return redirect(url_for('admin.manage_room', room_id=room_id))


# 管理预订
@admin_bp.route('/bookings')
@admin_required
def manage_bookings():
    # 获取搜索参数
    search_query = request.args.get('search', '').strip()

    # 分页配置
    PER_PAGE = 10
    try:
        current_page = int(request.args.get('page', 1))
        current_page = max(1, current_page)
    except ValueError:
        current_page = 1

    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    try:
        # 构建查询条件
        query_conditions = []
        query_params = []

        # 添加搜索条件
        if search_query:
            query_conditions.append("""
                (u.username LIKE %s OR r.name LIKE %s OR o.status LIKE %s)
            """)
            search_term = f"%{search_query}%"
            query_params.extend([search_term, search_term, search_term])

        # 查询符合条件的总记录数
        count_query = """
            SELECT COUNT(*) as total 
            FROM bookings o 
            JOIN users u ON o.user_id = u.id 
            JOIN rooms r ON o.room_id = r.id
        """
        if query_conditions:
            count_query += " WHERE " + " AND ".join(query_conditions)

        cursor.execute(count_query, query_params.copy())
        total_items = cursor.fetchone()['total']

        # 计算分页参数
        total_pages = (total_items + PER_PAGE - 1) // PER_PAGE
        current_page = min(current_page, total_pages) if total_pages > 0 else 1
        offset = (current_page - 1) * PER_PAGE

        # 查询当前页的预订数据
        base_query = """
            SELECT o.*, u.username, r.name as room_name, r.room_type 
            FROM bookings o 
            JOIN users u ON o.user_id = u.id 
            JOIN rooms r ON o.room_id = r.id
        """

        if query_conditions:
            data_query = f"{base_query} WHERE {' AND '.join(query_conditions)} ORDER BY o.created_at DESC LIMIT %s OFFSET %s"
        else:
            data_query = f"{base_query} ORDER BY o.created_at DESC LIMIT %s OFFSET %s"

        query_params.extend([PER_PAGE, offset])
        cursor.execute(data_query, query_params)
        bookings = cursor.fetchall()

        # 生成页码导航范围
        page_range = []
        if total_pages <= 5:
            page_range = list(range(1, total_pages + 1))
        else:
            page_range.append(1)
            if current_page > 3:
                page_range.append('...')

            start_page = max(2, current_page - 2)
            end_page = min(total_pages - 1, current_page + 2)
            page_range.extend(range(start_page, end_page + 1))

            if current_page < total_pages - 2:
                page_range.append('...')
            page_range.append(total_pages)

        # 计算当前页显示的记录范围
        start_item = offset + 1 if total_items > 0 else 0
        end_item = min(offset + PER_PAGE, total_items)

        return render_template('admin/bookings.html',
                               bookings=bookings,
                               search_query=search_query,
                               total_items=total_items,
                               current_page=current_page,
                               total_pages=total_pages,
                               page_range=page_range,
                               start_item=start_item,
                               end_item=end_item)
    except Exception as e:
        flash(f'获取预订记录失败: {str(e)}', 'danger')
        return render_template('admin/bookings.html',
                               bookings=[],
                               search_query=search_query,
                               total_items=0,
                               current_page=1,
                               total_pages=0,
                               page_range=[],
                               start_item=0,
                               end_item=0)
    finally:
        cursor.close()
        conn.close()


# 更新预订状态
@admin_bp.route('/bookings/update_status/<int:booking_id>', methods=['POST'])
@admin_required
def update_booking_status(booking_id):
    status = request.form['status'].lower().strip()
    delete_statuses = ['checked_out', 'cancelled']

    conn = get_db_connection()
    cursor = conn.cursor()

    try:
        # 获取关联的房间ID
        cursor.execute("SELECT room_id FROM bookings WHERE id = %s", (booking_id,))
        room_result = cursor.fetchone()
        if not room_result:
            flash('未找到关联的房间信息', 'error')
            return redirect(url_for('admin.manage_bookings'))
        room_id = room_result[0]

        if status in delete_statuses:
            # 退房或取消预订：删除记录并更新房间状态
            cursor.execute("DELETE FROM bookings WHERE id = %s", (booking_id,))
            cursor.execute("UPDATE rooms SET status = 'available' WHERE id = %s", (room_id,))
            flash('预订已删除，房间已恢复为可预订状态', 'success')
        else:
            # 更新预订状态并同步房间状态
            cursor.execute("UPDATE bookings SET status = %s WHERE id = %s", (status, booking_id))

            if status == 'checked_in':
                cursor.execute("UPDATE rooms SET status = 'occupied' WHERE id = %s", (room_id,))
                flash('预订状态已更新为已入住，房间状态已同步', 'success')
            elif status == 'confirmed':
                cursor.execute("UPDATE rooms SET status = 'booked' WHERE id = %s", (room_id,))
                flash('预订状态已更新为已确认，房间已标记为已预订', 'success')

        conn.commit()
    except Exception as e:
        conn.rollback()
        flash(f'操作失败: {str(e)}', 'danger')
    finally:
        cursor.close()
        conn.close()

    return redirect(url_for('admin.manage_bookings'))


# 管理反馈
@admin_bp.route('/feedbacks')
@admin_required
def manage_feedbacks():
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    try:
        cursor.execute("""
            SELECT f.*, u.username 
            FROM feedbacks f 
            JOIN users u ON f.user_id = u.id 
            ORDER BY f.created_at DESC
        """)
        feedbacks = cursor.fetchall()
        return render_template('admin/feedbacks.html', feedbacks=feedbacks)
    except Exception as e:
        flash(f'获取反馈列表失败: {str(e)}', 'danger')
        return render_template('admin/feedbacks.html', feedbacks=[])
    finally:
        cursor.close()
        conn.close()


# 更新反馈状态
@admin_bp.route('/feedbacks/update_status/<int:feedback_id>', methods=['POST'])
@admin_required
def update_feedback_status(feedback_id):
    status = request.form['status']

    conn = get_db_connection()
    cursor = conn.cursor()

    try:
        if status == 'resolved':
            cursor.execute("""
                UPDATE feedbacks SET 
                    status = %s, 
                    resolved_at = NOW() 
                WHERE id = %s
            """, (status, feedback_id))
        else:
            cursor.execute("UPDATE feedbacks SET status = %s WHERE id = %s", (status, feedback_id))

        conn.commit()
        flash('反馈状态已更新', 'success')
    except Exception as e:
        conn.rollback()
        flash(f'操作失败: {str(e)}', 'danger')
    finally:
        cursor.close()
        conn.close()

    return redirect(url_for('admin.manage_feedbacks'))


# 管理账单
@admin_bp.route('/bills')
@admin_required
def manage_bills():
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    try:
        cursor.execute("""
            SELECT b.*, u.username, o.check_in, o.check_out, o.status as booking_status,
                   r.name as room_name, r.room_type
            FROM bills b 
            JOIN bookings o ON b.booking_id = o.id 
            JOIN users u ON o.user_id = u.id 
            JOIN rooms r ON o.room_id = r.id 
            ORDER BY b.created_at DESC
        """)
        bills = cursor.fetchall()
        return render_template('admin/bills.html', bills=bills)
    except Exception as e:
        flash(f'获取账单列表失败: {str(e)}', 'danger')
        return render_template('admin/bills.html', bills=[])
    finally:
        cursor.close()
        conn.close()


# 更新账单状态
@admin_bp.route('/bills/update_status/<int:bill_id>', methods=['POST'])
@admin_required
def update_bill_status(bill_id):
    status = request.form['status'].lower().strip()
    paid_status = 'paid'

    conn = get_db_connection()
    cursor = conn.cursor()

    try:
        # 获取关联的预订和房间信息
        cursor.execute("""
            SELECT o.id as booking_id, o.status as booking_status, o.room_id
            FROM bills b 
            JOIN bookings o ON b.booking_id = o.id 
            WHERE b.id = %s
        """, (bill_id,))
        bill_relation = cursor.fetchone()

        if bill_relation:
            booking_id, booking_status, room_id = bill_relation

            # 当账单支付且预订已入住时，更新房间状态
            if status == paid_status and booking_status == 'checked_in':
                cursor.execute("UPDATE rooms SET status = 'occupied' WHERE id = %s", (room_id,))
                flash('账单已标记为已支付，房间状态已更新为已入住', 'info')

        # 更新账单状态
        if status == paid_status:
            cursor.execute("DELETE FROM bills WHERE id = %s", (bill_id,))
            flash('已支付账单已删除', 'success')
        else:
            cursor.execute("""
                UPDATE bills SET 
                    status = %s,
                    updated_at = NOW() 
                WHERE id = %s
            """, (status, bill_id))
            flash('账单状态已更新', 'success')

        conn.commit()
    except Exception as e:
        conn.rollback()
        flash(f'操作失败: {str(e)}', 'danger')
    finally:
        cursor.close()
        conn.close()

    return redirect(url_for('admin.manage_bills'))


# 删除账单
@admin_bp.route('/bill-del/<int:bill_id>', methods=['POST', 'GET'])
@admin_required
def delete_bill(bill_id):
    conn = get_db_connection()
    cursor = conn.cursor()

    try:
        cursor.execute("DELETE FROM bills WHERE id = %s", (bill_id,))
        conn.commit()
        flash('账单已删除', 'success')
    except Exception as e:
        conn.rollback()
        flash(f'操作失败: {str(e)}', 'danger')
    finally:
        cursor.close()
        conn.close()

    return redirect(url_for('admin.manage_bills'))


# ============================================================================
# 用户管理相关路由
# ============================================================================

# 用户列表页面
@admin_bp.route('/users')
@admin_required
def manage_users():
    """用户管理页面"""
    # 获取搜索和筛选参数
    search_query = request.args.get('search', '').strip()
    role_filter = request.args.get('role', 'all')

    # 分页配置
    PER_PAGE = 15
    try:
        current_page = int(request.args.get('page', 1))
        current_page = max(1, current_page)
    except ValueError:
        current_page = 1

    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    try:
        # 构建查询条件
        query_conditions = []
        query_params = []

        # 搜索条件
        if search_query:
            query_conditions.append("""
                (username LIKE %s OR email LIKE %s OR phone LIKE %s)
            """)
            search_term = f"%{search_query}%"
            query_params.extend([search_term, search_term, search_term])

        # 角色筛选
        if role_filter != 'all':
            query_conditions.append("role = %s")
            query_params.append(role_filter)

        # 查询总数
        count_query = "SELECT COUNT(*) as total FROM users"
        if query_conditions:
            count_query += " WHERE " + " AND ".join(query_conditions)

        cursor.execute(count_query, query_params.copy())
        total_items = cursor.fetchone()['total']

        # 计算分页
        total_pages = (total_items + PER_PAGE - 1) // PER_PAGE
        current_page = min(current_page, total_pages) if total_pages > 0 else 1
        offset = (current_page - 1) * PER_PAGE

        # 查询当前页数据
        data_query = "SELECT id, username, email, phone, role, created_at FROM users"
        if query_conditions:
            data_query += " WHERE " + " AND ".join(query_conditions)
        data_query += " ORDER BY created_at DESC LIMIT %s OFFSET %s"

        query_params.extend([PER_PAGE, offset])
        cursor.execute(data_query, query_params)
        users = cursor.fetchall()

        # 生成页码范围
        page_range = []
        if total_pages <= 5:
            page_range = list(range(1, total_pages + 1))
        else:
            page_range.append(1)
            if current_page > 3:
                page_range.append('...')
            start_page = max(2, current_page - 2)
            end_page = min(total_pages - 1, current_page + 2)
            page_range.extend(range(start_page, end_page + 1))
            if current_page < total_pages - 2:
                page_range.append('...')
            page_range.append(total_pages)

        # 计算显示范围
        start_item = offset + 1 if total_items > 0 else 0
        end_item = min(offset + PER_PAGE, total_items)

        return render_template('admin/users.html',
                               users=users,
                               search_query=search_query,
                               role_filter=role_filter,
                               total_items=total_items,
                               current_page=current_page,
                               total_pages=total_pages,
                               page_range=page_range,
                               start_item=start_item,
                               end_item=end_item)
    except Exception as e:
        current_app.logger.error(f'获取用户列表失败: {str(e)}')
        flash(f'获取用户列表失败: {str(e)}', 'danger')
        return render_template('admin/users.html',
                               users=[],
                               search_query=search_query,
                               role_filter=role_filter,
                               total_items=0,
                               current_page=1,
                               total_pages=0,
                               page_range=[],
                               start_item=0,
                               end_item=0)
    finally:
        cursor.close()
        conn.close()


# 获取用户信息API
@admin_bp.route('/api/users/<int:user_id>', methods=['GET'])
@admin_required
def get_user_info(user_id):
    """获取单个用户详细信息"""
    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        cursor.execute("""
            SELECT id, username, email, phone, role, created_at 
            FROM users WHERE id = %s
        """, (user_id,))
        user = cursor.fetchone()

        cursor.close()
        conn.close()

        if user:
            # 格式化日期
            if user['created_at']:
                user['created_at'] = user['created_at'].strftime('%Y-%m-%d %H:%M:%S')

            return jsonify({
                'success': True,
                'user': user
            })
        else:
            return jsonify({
                'success': False,
                'message': '用户不存在'
            }), 404

    except Exception as e:
        current_app.logger.error(f'获取用户信息失败: {str(e)}')
        return jsonify({
            'success': False,
            'message': str(e)
        }), 500


# 更新用户信息API
@admin_bp.route('/api/users/<int:user_id>', methods=['PUT'])
@admin_required
def update_user_info(user_id):
    """更新用户信息"""
    try:
        data = request.get_json()
        if not data:
            return jsonify({
                'success': False,
                'message': '无效的请求数据'
            }), 400

        # 可更新的字段
        username = data.get('username', '').strip()
        email = data.get('email', '').strip()
        phone = data.get('phone', '').strip()
        role = data.get('role', '').strip()

        # 验证必填字段
        if not username or not email:
            return jsonify({
                'success': False,
                'message': '用户名和邮箱为必填项'
            }), 400

        # 验证角色
        if role not in ['user', 'admin']:
            return jsonify({
                'success': False,
                'message': '无效的角色类型'
            }), 400

        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        # 检查用户名是否已被其他用户使用
        cursor.execute("""
            SELECT id FROM users WHERE username = %s AND id != %s
        """, (username, user_id))
        if cursor.fetchone():
            cursor.close()
            conn.close()
            return jsonify({
                'success': False,
                'message': '用户名已被使用'
            }), 400

        # 检查邮箱是否已被其他用户使用
        cursor.execute("""
            SELECT id FROM users WHERE email = %s AND id != %s
        """, (email, user_id))
        if cursor.fetchone():
            cursor.close()
            conn.close()
            return jsonify({
                'success': False,
                'message': '邮箱已被使用'
            }), 400

        # 更新用户信息
        cursor.execute("""
            UPDATE users 
            SET username = %s, email = %s, phone = %s, role = %s
            WHERE id = %s
        """, (username, email, phone, role, user_id))

        conn.commit()
        cursor.close()
        conn.close()

        return jsonify({
            'success': True,
            'message': '用户信息更新成功'
        })

    except Exception as e:
        current_app.logger.error(f'更新用户信息失败: {str(e)}')
        return jsonify({
            'success': False,
            'message': str(e)
        }), 500


# 更新用户角色API（表单提交）
@admin_bp.route('/users/update_role/<int:user_id>', methods=['POST'])
@admin_required
def update_user_role(user_id):
    """更新用户角色（表单提交）"""
    role = request.form.get('role', '').strip()

    if role not in ['user', 'admin']:
        flash('无效的角色类型', 'danger')
        return redirect(url_for('admin.manage_users'))

    conn = get_db_connection()
    cursor = conn.cursor()

    try:
        cursor.execute("""
            UPDATE users SET role = %s WHERE id = %s
        """, (role, user_id))
        conn.commit()
        flash('用户角色已更新', 'success')
    except Exception as e:
        conn.rollback()
        flash(f'操作失败: {str(e)}', 'danger')
    finally:
        cursor.close()
        conn.close()

    return redirect(url_for('admin.manage_users'))


# 删除用户API
@admin_bp.route('/api/users/<int:user_id>', methods=['DELETE'])
@admin_required
def delete_user(user_id):
    """删除用户"""
    try:
        # 防止删除当前登录的管理员
        if session.get('user_id') == user_id:
            return jsonify({
                'success': False,
                'message': '不能删除当前登录的用户'
            }), 400

        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        # 检查用户是否有关联的预订
        cursor.execute("""
            SELECT COUNT(*) as count FROM bookings WHERE user_id = %s
        """, (user_id,))
        booking_count = cursor.fetchone()['count']

        if booking_count > 0:
            cursor.close()
            conn.close()
            return jsonify({
                'success': False,
                'message': f'无法删除：该用户有 {booking_count} 个关联预订'
            }), 400

        # 删除用户
        cursor.execute("DELETE FROM users WHERE id = %s", (user_id,))
        conn.commit()
        cursor.close()
        conn.close()

        return jsonify({
            'success': True,
            'message': '用户已删除'
        })

    except Exception as e:
        current_app.logger.error(f'删除用户失败: {str(e)}')
        return jsonify({
            'success': False,
            'message': str(e)
        }), 500


# ============================================================================
# 数据报表API路由
# ============================================================================

# 获取报表数据API
@admin_bp.route('/api/reports/data', methods=['GET'])
@admin_required
def get_report_data():
    """获取报表数据（使用查询参数指定日期范围）"""
    try:
        # 获取日期参数
        start_date_str = request.args.get('start_date')
        end_date_str = request.args.get('end_date')

        # 默认日期范围：最近30天
        if not start_date_str or not end_date_str:
            end = datetime.now()
            start = end - timedelta(days=30)
        else:
            try:
                start = datetime.strptime(start_date_str, '%Y-%m-%d')
                end = datetime.strptime(end_date_str, '%Y-%m-%d')
            except ValueError:
                return jsonify({
                    'success': False,
                    'message': '日期格式错误，请使用 YYYY-MM-DD 格式'
                }), 400

        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        # 获取最早和最晚的日期
        cursor.execute("""
            SELECT MIN(DATE(created_at)) as min_date, 
                   MAX(DATE(created_at)) as max_date 
            FROM bookings 
            WHERE status != 'cancelled'
        """)
        date_range = cursor.fetchone()

        # 如果没有数据，使用默认范围
        if not date_range['min_date'] or not date_range['max_date']:
            end = datetime.now()
            start = end - timedelta(days=7)
        else:
            # 处理日期类型
            if isinstance(date_range['min_date'], date) and not isinstance(date_range['min_date'], datetime):
                db_start = datetime.combine(date_range['min_date'], datetime.min.time())
            else:
                db_start = date_range['min_date']

            if isinstance(date_range['max_date'], date) and not isinstance(date_range['max_date'], datetime):
                db_end = datetime.combine(date_range['max_date'], datetime.max.time())
            else:
                db_end = date_range['max_date']

            # 使用请求的日期范围与数据库实际范围的交集
            start = max(start, db_start)
            end = min(end, db_end)

        # 生成日期标签
        date_labels = []
        current_date = start.date()
        end_date = end.date()
        while current_date <= end_date:
            date_labels.append(current_date.strftime('%m-%d'))
            current_date += timedelta(days=1)

        # 1. 入住率数据
        occupancy_data = []
        cursor.execute("SELECT COUNT(*) as total FROM rooms")
        total_rooms = cursor.fetchone()['total'] or 0

        if total_rooms > 0:
            for d in date_labels:
                date_obj = datetime.strptime(f"{start.year}-{d}", '%Y-%m-%d').date()
                if date_obj < start.date() and start.year < end.year:
                    date_obj = datetime.strptime(f"{start.year + 1}-{d}", '%Y-%m-%d').date()

                date_str = date_obj.strftime('%Y-%m-%d')
                cursor.execute("""
                    SELECT COUNT(DISTINCT room_id) as booked_count 
                    FROM bookings 
                    WHERE status != 'cancelled'
                    AND DATE(check_in) <= %s 
                    AND DATE(check_out) > %s
                """, (date_str, date_str))
                booked_rooms = cursor.fetchone()['booked_count'] or 0
                occupancy_data.append(round((booked_rooms / total_rooms) * 100, 1))
        else:
            occupancy_data = [0] * len(date_labels)

        # 2. 应收金额数据
        revenue_data = []
        for d in date_labels:
            date_obj = datetime.strptime(f"{start.year}-{d}", '%Y-%m-%d').date()
            if date_obj < start.date() and start.year < end.year:
                date_obj = datetime.strptime(f"{start.year + 1}-{d}", '%Y-%m-%d').date()
            date_str = date_obj.strftime('%Y-%m-%d')

            cursor.execute("""
                SELECT COALESCE(SUM(total_price), 0) as revenue 
                FROM bookings 
                WHERE status != 'cancelled'
                AND DATE(created_at) = %s
            """, (date_str,))
            revenue = cursor.fetchone()['revenue'] or 0
            revenue_data.append(int(revenue))

        # 3. 房间类型占比数据
        room_type_data = []
        room_type_labels = []
        type_name_map = {
            'standard': '标准间',
            'deluxe': '豪华间',
            'suite': '套房',
            'family': '家庭房',
            'game': '电竞房'
        }

        cursor.execute("""
            SELECT room_type, COUNT(*) as count 
            FROM rooms 
            GROUP BY room_type
        """)
        room_types = cursor.fetchall()
        for item in room_types:
            room_type_labels.append(type_name_map.get(item['room_type'], item['room_type']))
            room_type_data.append(item['count'])

        # 4. 房间利用率数据
        utilization_data = []
        utilization_labels = room_type_labels.copy()
        start_str = start.strftime('%Y-%m-%d %H:%M:%S')
        end_str = end.strftime('%Y-%m-%d %H:%M:%S')
        period_length = (end.date() - start.date()).days + 1

        for item in room_types:
            room_type = item['room_type']
            type_rooms = item['count'] or 0

            if type_rooms == 0:
                utilization_data.append(0)
                continue

            cursor.execute("SELECT id FROM rooms WHERE room_type = %s", (room_type,))
            type_room_ids = [row['id'] for row in cursor.fetchall()]

            if not type_room_ids:
                utilization_data.append(0)
                continue

            placeholders = ', '.join(['%s'] * len(type_room_ids))
            cursor.execute(f"""
                SELECT room_id, check_in, check_out 
                FROM bookings 
                WHERE room_id IN ({placeholders})
                AND status != 'cancelled'
                AND check_in < %s
                AND check_out > %s
            """, type_room_ids + [end_str, start_str])

            bookings = cursor.fetchall()
            booked_days = 0

            for b in bookings:
                if isinstance(b['check_in'], str):
                    booking_start = datetime.strptime(b['check_in'], '%Y-%m-%d %H:%M:%S')
                else:
                    booking_start = b['check_in']

                if isinstance(b['check_out'], str):
                    booking_end = datetime.strptime(b['check_out'], '%Y-%m-%d %H:%M:%S')
                else:
                    booking_end = b['check_out']

                booking_start = max(booking_start, start)
                booking_end = min(booking_end, end)

                days = (booking_end - booking_start).days
                booked_days += max(0, days)

            total_days = type_rooms * period_length
            utilization_rate = round((booked_days / total_days) * 100, 1) if total_days > 0 else 0
            utilization_data.append(utilization_rate)

        # 计算总体统计
        prev_start = start - (end - start)
        prev_end = start

        overall_stats = calculate_overall_stats(
            cursor, start, end, prev_start, prev_end,
            occupancy_data, revenue_data, utilization_data,
            room_types, start_str, end_str
        )

        cursor.close()
        conn.close()

        return jsonify({
            'success': True,
            'data': {
                'date_labels': date_labels,
                'occupancy_data': occupancy_data,
                'revenue_data': revenue_data,
                'room_type_data': room_type_data,
                'room_type_labels': room_type_labels,
                'utilization_data': utilization_data,
                'utilization_labels': utilization_labels,
                'overall_stats': overall_stats
            }
        })

    except Exception as e:
        current_app.logger.error(f'获取报表数据失败: {str(e)}')
        return jsonify({
            'success': False,
            'message': str(e)
        }), 500


# ============================================================================
# 系统内容管理路由
# ============================================================================

# 系统内容管理主页面
@admin_bp.route('/content')
@admin_required
def content_management():
    """系统内容管理页面"""
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    try:
        # 获取网站基本设置
        cursor.execute("""
            SELECT * FROM site_settings 
            ORDER BY id DESC LIMIT 1
        """)
        site_settings = cursor.fetchone()
        
        # 获取轮播图列表
        cursor.execute("""
            SELECT * FROM banners 
            ORDER BY sort_order ASC, id DESC
        """)
        banners = cursor.fetchall()
        
        # 获取关于我们内容
        cursor.execute("""
            SELECT * FROM about_content 
            ORDER BY id DESC LIMIT 1
        """)
        about_content = cursor.fetchone()
        
        return render_template('admin/content.html',
                             site_settings=site_settings,
                             banners=banners,
                             about_content=about_content)
    
    except Exception as e:
        flash(f'加载内容管理失败: {str(e)}', 'error')
        return redirect(url_for('admin.dashboard'))
    finally:
        cursor.close()
        conn.close()


# 更新网站基本设置
@admin_bp.route('/content/site-settings', methods=['POST'])
@admin_required
def update_site_settings():
    """更新网站基本设置"""
    try:
        site_name = request.form.get('site_name', '').strip()
        site_description = request.form.get('site_description', '').strip()
        contact_phone = request.form.get('contact_phone', '').strip()
        contact_email = request.form.get('contact_email', '').strip()
        address = request.form.get('address', '').strip()
        
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)
        
        # 检查是否存在设置
        cursor.execute("SELECT id FROM site_settings LIMIT 1")
        existing = cursor.fetchone()
        
        if existing:
            # 更新现有设置
            cursor.execute("""
                UPDATE site_settings SET 
                    site_name = %s,
                    site_description = %s,
                    contact_phone = %s,
                    contact_email = %s,
                    address = %s,
                    updated_at = NOW()
                WHERE id = %s
            """, (site_name, site_description, contact_phone, 
                  contact_email, address, existing['id']))
        else:
            # 插入新设置
            cursor.execute("""
                INSERT INTO site_settings 
                (site_name, site_description, contact_phone, contact_email, address)
                VALUES (%s, %s, %s, %s, %s)
            """, (site_name, site_description, contact_phone, contact_email, address))
        
        conn.commit()
        flash('网站设置已更新', 'success')
        
    except Exception as e:
        flash(f'更新失败: {str(e)}', 'error')
    finally:
        cursor.close()
        conn.close()
    
    return redirect(url_for('admin.content_management'))


# 添加轮播图
@admin_bp.route('/content/banner/add', methods=['POST'])
@admin_required
def add_banner():
    """添加轮播图"""
    try:
        title = request.form.get('title', '').strip()
        link_url = request.form.get('link_url', '').strip()
        sort_order = int(request.form.get('sort_order', 0))
        is_active = 1 if request.form.get('is_active') else 0
        
        # 处理图片上传
        if 'image' not in request.files:
            flash('请上传轮播图图片', 'error')
            return redirect(url_for('admin.content_management'))
        
        file = request.files['image']
        if file.filename == '':
            flash('请选择图片文件', 'error')
            return redirect(url_for('admin.content_management'))
        
        if file and allowed_file(file.filename):
            # 保存图片
            filename = secure_filename(file.filename)
            ext = filename.rsplit('.', 1)[1].lower()
            new_filename = f"banner_{int(time.time())}.{ext}"
            
            upload_folder = os.path.join(current_app.root_path, 'static/uploads/banners')
            os.makedirs(upload_folder, exist_ok=True)
            
            file_path = os.path.join(upload_folder, new_filename)
            file.save(file_path)
            
            # 保存到数据库
            conn = get_db_connection()
            cursor = conn.cursor()
            
            cursor.execute("""
                INSERT INTO banners (title, image_url, link_url, sort_order, is_active)
                VALUES (%s, %s, %s, %s, %s)
            """, (title, f'banners/{new_filename}', link_url, sort_order, is_active))
            
            conn.commit()
            cursor.close()
            conn.close()
            
            flash('轮播图已添加', 'success')
        else:
            flash('不支持的文件类型', 'error')
    
    except Exception as e:
        flash(f'添加失败: {str(e)}', 'error')
    
    return redirect(url_for('admin.content_management'))


# 删除轮播图
@admin_bp.route('/content/banner/delete/<int:banner_id>', methods=['POST'])
@admin_required
def delete_banner(banner_id):
    """删除轮播图"""
    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)
        
        # 获取图片路径
        cursor.execute("SELECT image_url FROM banners WHERE id = %s", (banner_id,))
        banner = cursor.fetchone()
        
        if banner:
            # 删除图片文件
            image_path = os.path.join(current_app.root_path, 'static/uploads', banner['image_url'])
            if os.path.exists(image_path):
                os.remove(image_path)
            
            # 删除数据库记录
            cursor.execute("DELETE FROM banners WHERE id = %s", (banner_id,))
            conn.commit()
            
            flash('轮播图已删除', 'success')
        else:
            flash('轮播图不存在', 'error')
    
    except Exception as e:
        flash(f'删除失败: {str(e)}', 'error')
    finally:
        cursor.close()
        conn.close()
    
    return redirect(url_for('admin.content_management'))


# 更新关于我们内容
@admin_bp.route('/content/about', methods=['POST'])
@admin_required
def update_about_content():
    """更新关于我们内容"""
    try:
        title = request.form.get('about_title', '').strip()
        content = request.form.get('about_content', '').strip()
        
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)
        
        # 检查是否存在内容
        cursor.execute("SELECT id FROM about_content LIMIT 1")
        existing = cursor.fetchone()
        
        if existing:
            cursor.execute("""
                UPDATE about_content SET 
                    title = %s,
                    content = %s,
                    updated_at = NOW()
                WHERE id = %s
            """, (title, content, existing['id']))
        else:
            cursor.execute("""
                INSERT INTO about_content (title, content)
                VALUES (%s, %s)
            """, (title, content))
        
        conn.commit()
        flash('关于我们内容已更新', 'success')
        
    except Exception as e:
        flash(f'更新失败: {str(e)}', 'error')
    finally:
        cursor.close()
        conn.close()
    
    return redirect(url_for('admin.content_management'))